

ALTER   proc sp_return_inventory_lot_qty
@customerShipping_id int
As
begin
	declare @line_item_id int
	declare @line_item_id_cursor cursor 
	declare @lot_number_info_cursor cursor
	declare @lot_id int
	declare @pull_qty int
	declare @balance_qty int
	declare @shipped_qty int

	set @line_item_id_cursor = cursor for
	select cslit_id from customer_shipping_line_item_table where customerShipping_id = @customerShipping_id
	open @line_item_id_cursor
		fetch next from @line_item_id_cursor into @line_item_id
		while @@fetch_status = 0
		begin 
			set @lot_number_info_cursor = cursor for
			select Lot_ID, CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @line_item_id
			open @lot_number_info_cursor
				fetch next from @lot_number_info_cursor into @lot_id, @pull_qty
				while @@fetch_status = 0
				begin
					select @shipped_qty = IL_Qty_Shipped, @balance_qty = IL_Qty_Balance From Inventory_Lot Where Lot_ID = @lot_id
					update Inventory_lot set IL_Qty_Shipped = (@shipped_qty-@pull_qty), IL_Qty_Balance = (@balance_qty+@pull_qty) Where Lot_ID = @lot_id
					fetch next from @lot_number_info_cursor into @lot_id, @pull_qty
				end
			close @lot_number_info_cursor
			deallocate @lot_number_info_cursor
			fetch next from @line_item_id_cursor into @line_item_id
		end
	close @line_item_id_cursor
	deallocate @line_item_id_cursor

end

